create database TT
go
use TT
go
create table orders(
orderId int primary key identity,
orderDate datetime
);
create table orderItem(
itemId int,
orderid int references orders(orderId),
itemType nvarchar(10),
itemName nvarchar(10),
theNumber int check(theNumber>0),
themoney money check(themoney>0)
);
insert into orders(orderDate)
values
(2008-01-12),
(2008-02-10),
(2008-02-15),
(2008-03-10);
insert into orderItem(itemId,orderid,itemType,itemName,theNumber,themoney)
values
(1,1,'文具','笔',72,2),
(2,1,'文具','尺',10,1),
(3,1,'体育用品','篮球',1,56),
(4,2,'文具','笔',36,2),
(5,2,'文具','固体胶',20,3),
(6,2,'日常用品','透明胶',2,1),
(7,2,'体育用品','羽毛球',20,3),
(8,3,'文具','订书机',20,3),
(9,3,'文具','订书机',10,3),
(10,3,'文具','裁纸刀',5,5),
(11,4,'文具','笔',20,2),
(12,4,'文具','信纸',50,1),
(13,4,'日常用品','毛巾',4,5),
(14,4,'日常用品','透明胶',30,1),
(15,4,'体育用品','羽毛球',20,3);
--1.查询所有订单订购的所有物品数量总和
select sum(theNumber) as 数量总和 from orderItem 
--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select orderId, sum(theNumber)as 物品数量,avg(theMoney) as 平均单价 from orderItem 
where orderid<3
group by orderid
having avg(theMoney)<10;
--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
select orderId ,sum(theNumber) as 物品数量,avg(theMoney)as 平均单价 from orderItem
group by orderId
having avg(themoney)<10 and sum(theNumber)>10;
--4.查询每种类别的产品分别订购了几次
select itemType,count(itemType) as 订购次数 from orderItem group by itemType;
--5.查询每种类别的产品的订购总数量在100以上的订购总数量和平均单价
select itemType,sum(theNumber),avg(theMoney) from orderItem group by itemType 
having sum(theNumber)>100;
--6.查询每种产品的订购次数，订购总数量和订购的平均单价
select itemName,count(itemName) as 订购次数 ,sum(theNumber) as 订购总数,avg(theMoney) as 平均单价 from orderItem group by itemName;
--1.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价
select orders.orderId,orderDate,itemType,itemName,theNumber,theMoney from orders join orderItem on orders.orderId=orderItem.orderid
--2.查询订购数量大于50的订单的编号，订单日期，订购产品的类别和订购的产品名称
select orders.orderId,orderDate,itemType,itemName from orders join orderItem on orders.orderId=orderItem.orderid where theNumber>50;
--3.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orders.orderId,orderDate,itemType,itemName,theNumber,theMoney,sum(theNumber*theMoney) 订购总价 from orders join orderItem on orders.orderId=orderItem.orderid group by orders.orderId,orderDate,itemType,itemName,theNumber,theMoney
--4.查询单价大于等于5并且数量大于等于50的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orders.orderId,orderDate,itemType,itemName,theNumber,theMoney,sum(theNumber*theMoney) 订购总价 
from orders join orderItem on orders.orderId=orderItem.orderid 
where theMoney>=5 and theNumber>=50
group by orders.orderId,orderDate,itemType,itemName,theNumber,theMoney 
--5.查询每个订单分别订购了几个产品
select orderId 编号,count(itemName)订购了产品数 from orderItem group by orderId
--6.查询每个订单里的每个类别的产品分别订购了几次和总数量
select orderId 订单编号,itemType 产品类别,count(itemName)订购次数,sum(theNumber) 总数量 from orderItem group by orderId,itemType